# -*- coding: utf-8 -*-
import uuid
import os

from sqlalchemy import func, or_
from sqlalchemy.orm import selectinload, joinedload
from loguru import logger

from db.session import with_session
from db.models.files import QAFile
from db.models.attachments import Attach2File
from db.models.attributes import FileAttributeAssociation, ApplicableObject, KnowledgeType, ApplicableArea, ElementClassification
from db.models.knowledge import File2Knowledge, KnowledgeBase
from utils.file_utils import to_rel_path, to_abs_path


""" ================================= QAFile ================================ """


# 获取 知识库中 所有的文件（包含删除状态过滤）
@with_session
def db_get_files_in_kb_with_deleted(session, kb_id):
    """获取知识库中所有文件，包括已删除的"""
    kbs = session.query(QAFile)\
        .join(
            File2Knowledge,
            or_(
                File2Knowledge.file_id == func.BIN_TO_UUID(QAFile.id),
                File2Knowledge.file_id == func.REPLACE(func.BIN_TO_UUID(QAFile.id), '-', ''),
                File2Knowledge.file_id == func.HEX(QAFile.id),
                File2Knowledge.file_id == func.LOWER(func.HEX(QAFile.id))
            )
        ) \
        .options(joinedload(QAFile.attributes)) \
        .filter(File2Knowledge.knowledge_id == kb_id) \
        .all()
    return list(kbs)


@with_session
def db_get_qa_files(session, *, name_like: str = None, offset: int = 0, limit: int = 50):
    query = session.query(QAFile)\
        .options(selectinload(QAFile.attributes))\
        .filter(QAFile.is_delete.is_(False))
    if name_like:
        query = query.filter(QAFile.name.ilike(f"%{name_like}%"))
    total = query.count()
    items = query.order_by(QAFile.create_time.desc()).offset(offset).limit(limit).all()
    return items, total


@with_session
def db_get_qa_files_by_user_groups(session, user_group_ids, *, name_like: str = None, offset: int = 0, limit: int = 50):
    """根据用户组ID列表获取QA文件（通过知识库权限控制）"""
    if not user_group_ids:
        return [], 0
    
    query = session.query(QAFile)\
        .options(selectinload(QAFile.attributes))\
        .join(
            File2Knowledge,
            or_(
                File2Knowledge.file_id == func.BIN_TO_UUID(QAFile.id),
                File2Knowledge.file_id == func.REPLACE(func.BIN_TO_UUID(QAFile.id), '-', ''),
                File2Knowledge.file_id == func.HEX(QAFile.id),
                File2Knowledge.file_id == func.LOWER(func.HEX(QAFile.id))
            )
        )\
        .join(KnowledgeBase, File2Knowledge.knowledge_id == KnowledgeBase.id)\
        .filter(QAFile.is_delete.is_(False))\
        .filter(KnowledgeBase.is_delete.is_(False))\
        .filter(
            or_(
                KnowledgeBase.group_ids == [],
                func.json_contains(KnowledgeBase.group_ids, str(user_group_ids))
            )
        )\
        .distinct()
    if name_like:
        query = query.filter(QAFile.name.ilike(f"%{name_like}%"))
    total = query.count()
    items = query.order_by(QAFile.create_time.desc()).offset(offset).limit(limit).all()
    return items, total


# 查找 根据文件id查找
@with_session
def db_get_qa_file_by_id(session, file_id: uuid.UUID):
    if isinstance(file_id, str):
        file_id = uuid.UUID(file_id)
    return session.query(QAFile) \
        .options(selectinload(QAFile.attributes)) \
        .filter(QAFile.id == file_id) \
        .first()


# 查找 根据文件名称查找
@with_session
def db_get_qa_file_by_name(session, file_name):
    return session.query(QAFile) \
        .options(selectinload(QAFile.attributes)) \
        .filter(QAFile.hash_name == file_name, QAFile.is_delete.is_(False)) \
        .first()


# 新增 文件 对象
@with_session
def db_add_qa_file(session, user_id, file_name, origin_name, location, file_type, size, gb_num='', scope=''):
    f = session.query(QAFile).filter(QAFile.hash_name == file_name).first()
    if f:
        f.is_delete = False
        f.created_by = user_id
        # 仅保留hash_name
        f.hash_name = file_name
        f.name = origin_name if not origin_name.endswith(".pdf") else origin_name.replace(".pdf", "")
        f.location = to_rel_path(location)
        f.file_type = file_type
        f.file_size = size
        session.commit()
        return f, True
    new_file = QAFile(
        created_by=user_id,
        hash_name=file_name,
        name=origin_name if not origin_name.endswith(".pdf") else origin_name.replace(".pdf", ""),
        location=to_rel_path(location),
        file_type=file_type,
        file_size=size,
        gb_num=gb_num,
        applicable_scope=scope
    )
    session.add(new_file)
    session.commit()
    return new_file, False


# 增加 文件的属性关联
@with_session
def db_file_add_asso(session, file_id, attr_type, attr_ids):
    if not attr_ids:  # 如果attr_ids为空或None，直接返回
        return True
        
    if attr_type == "applicable_object":
        model_class = ApplicableObject
    elif attr_type == "knowledge_type":
        model_class = KnowledgeType
    elif attr_type == "applicable_area":
        model_class = ApplicableArea
    elif attr_type == "element_classification":
        model_class = ElementClassification
    else:
        return False

    # 读取已存在的关联，避免重复插入触发唯一键冲突
    exist_ids = {
        fa.attribute_id
        for fa in session.query(FileAttributeAssociation.attribute_id)
            .filter(
                FileAttributeAssociation.file_id == file_id,
                FileAttributeAssociation.attribute_type == attr_type
            ).all()
    }

    # 规范化待新增ID集合
    # if 0 in attr_ids:
    #     # 选择全部：取出所有属性ID
    #     all_ids = [a.id for a in session.query(model_class.id).all()]
    #     target_ids = set(all_ids)
    # else:
    #     target_ids = set(int(i) for i in attr_ids if i != 0)

    # 确保所有ID都是整数
    try:
        target_ids = set(int(i) for i in attr_ids)
    except (ValueError, TypeError) as e:
        logger.error(f"无效的属性ID列表: {attr_ids}, 错误: {e}")
        return False

    # 仅新增不存在的关联
    to_add_ids = target_ids - exist_ids
    if not to_add_ids:
        return True

    for attr_id in to_add_ids:
        association = FileAttributeAssociation(
            file_id=file_id,
            attribute_id=attr_id,
            attribute_type=attr_type
        )
        session.add(association)

    session.commit()
    return True


# 更新 文件 的适用范围
@with_session
def db_update_qa_file_scope_gb(session, file_id, file_name=None, gb_num=None, applicable_scope=None):
    file = session.query(QAFile)\
        .options(selectinload(QAFile.attributes))\
        .filter(QAFile.id == file_id)\
        .first()
    
    if not file:
        return None
        
    if file_name:
        file.name = file_name
    if gb_num:
        file.gb_num = gb_num
    if applicable_scope:
        file.applicable_scope = applicable_scope

    session.commit()
    return file


# 更新 四个文件小属性列表
@with_session
def db_update_qa_file_attributes(session, file_id, att_type, new_ids):
    if new_ids is None:
        return None

    current_associations = session.query(FileAttributeAssociation).filter(
        FileAttributeAssociation.file_id == file_id,
        FileAttributeAssociation.attribute_type == att_type
    ).all()

    current_ids = {assoc.attribute_id for assoc in current_associations}
    
    # 确保所有ID都是整数
    try:
        new_ids_set = set(int(i) for i in new_ids)
    except (ValueError, TypeError) as e:
        logger.error(f"无效的属性ID列表: {new_ids}, 错误: {e}")
        return None

    to_delete_ids = current_ids - new_ids_set
    if to_delete_ids:
        # 将集合转换为元组并使用局部变量
        to_delete_tuple = tuple(to_delete_ids)
        session.query(FileAttributeAssociation).filter(
            FileAttributeAssociation.file_id == file_id,
            FileAttributeAssociation.attribute_type == att_type,
            FileAttributeAssociation.attribute_id.in_(to_delete_tuple)  # 使用元组替代集合
        ).delete(synchronize_session=False)

    # 需要新增的关联 (保持原样)
    to_add_ids = new_ids_set - current_ids
    for attr_id in to_add_ids:
        new_assoc = FileAttributeAssociation(
            file_id=file_id,
            attribute_id=attr_id,
            attribute_type=att_type
        )
        session.add(new_assoc)

    session.commit()
    return True


@with_session
def db_update_qa_file_delete_state(session, file_id: uuid.UUID, delete):
    file = session.query(QAFile).get(file_id)
    if not file:
        return None
    file.is_delete = delete
    session.commit()
    return True


""" ================================= Attach2File ================================ """


@with_session
def db_add_qa_attach(session, origin_name, file_size, save_path, file_id, image_url):
    # 首先检查是否存在相同的download_link
    exist = session.query(Attach2File).filter(
        Attach2File.file_id == file_id,
        Attach2File.download_link == image_url
    ).first()
    
    if exist:
        # 如果存在但location为空或路径不正确，更新location
        if not exist.location or not os.path.exists(to_abs_path(exist.location)):
            exist.location = save_path
            exist.origin_name = origin_name
            session.commit()
            logger.info(f"Updated existing attach record location: {save_path}")
        return exist
    
    # 检查是否存在相同的location（避免重复保存）
    location_exist = session.query(Attach2File).filter(
        Attach2File.file_id == file_id,
        Attach2File.location == save_path
    ).first()
    
    if location_exist:
        return location_exist
    
    # 创建新记录
    attach = Attach2File(
        file_type=0,
        file_size=file_size,
        location=to_rel_path(save_path),
        origin_name=origin_name,
        content="",
        file_id=file_id,
        download_link=image_url
    )
    session.add(attach)
    session.commit()  # 提交事务确保数据一致性
    return attach


@with_session
def db_get_qa_img_by_id(session, img_id):
    if isinstance(img_id, str):
        img_id = uuid.UUID(img_id)
    return session.query(Attach2File).get(img_id)


@with_session
def db_get_qa_img_by_name(session, img_name):
    return session.query(Attach2File).filter(Attach2File.origin_name == img_name).first()


@with_session
def db_list_qa_attaches_by_file_id(session, file_id: uuid.UUID):
    """列出指定文件的所有附件记录"""
    return session.query(Attach2File).filter(Attach2File.file_id == file_id).all()


@with_session
def db_update_qa_attach(session, attach_id: uuid.UUID, *, location: str = None, download_link: str = None, origin_name: str = None):
    """更新附件的部分字段（存在则更新）"""
    if isinstance(attach_id, str):
        attach_id = uuid.UUID(attach_id)
    obj = session.query(Attach2File).get(attach_id)
    if not obj:
        return None
    updated = False
    if location is not None and location != obj.location:
        obj.location = to_rel_path(location)
        updated = True
    if download_link is not None and download_link != obj.download_link:
        obj.download_link = download_link
        updated = True
    if origin_name is not None and origin_name != obj.origin_name:
        obj.origin_name = origin_name
        updated = True
    if updated:
        session.add(obj)
        session.commit()
    return obj


@with_session
def delete_db_records_for_qa_file(session, file_obj):
    """完全删除文件及其所有关联数据"""
    try:
        # 1. 删除附件关联（仅对普通文件）
        if hasattr(file_obj, 'id') and hasattr(file_obj, '__tablename__') and file_obj.__tablename__ == 'qa_files':
            session.query(Attach2File).filter(Attach2File.file_id == file_obj.id).delete(synchronize_session=False)
            
            # 3. 删除文件属性关联（适用对象、知识类型、适用区域、要素分类）
            session.query(FileAttributeAssociation).filter(FileAttributeAssociation.file_id == file_obj.id).delete(synchronize_session=False)
        
        # 2. 删除文件-知识库关联
        session.query(File2Knowledge).filter(File2Knowledge.file_id == str(file_obj.id)).delete(synchronize_session=False)
        
        # 4. 删除文件本身
        file_obj.is_delete = True
        session.flush()
        
        return True
    except Exception as e:
        logger.error(f"删除文件关联数据失败: {str(e)}")
        raise


@with_session
def db_completely_delete_qa_file(session, file_id: uuid.UUID):
    """完全删除文件：包括数据库记录、本地文件和所有关联"""
    try:
        # 1. 获取文件对象（先尝试普通文件，再尝试双控文件）
        file_obj = session.query(QAFile).filter(QAFile.id == file_id).first()
        if not file_obj:
            return False, "文件不存在"
        
        # 2. 删除数据库中的所有关联记录
        delete_db_records_for_qa_file(file_obj)
        
        # 3. 删除本地文件
        # try:
        #     if file_obj.location and os.path.exists(file_obj.location):
        #         # 删除文件所在目录
        #         file_dir = os.path.dirname(file_obj.location)
        #         if os.path.exists(file_dir):
        #             import shutil
        #             shutil.rmtree(file_dir)
        #             logger.info(f"已删除本地文件目录: {file_dir}")
        # except Exception as e:
        #     logger.warning(f"删除本地文件失败: {str(e)}")
        
        # 4. 删除PDF转换文件（如果存在）
        # try:
        #     if file_obj.type in ['doc', 'docx']:
        #         pdf_path = file_obj.location.replace(f".{file_obj.type}", ".pdf")
        #         if os.path.exists(pdf_path):
        #             os.remove(pdf_path)
        #             logger.info(f"已删除PDF转换文件: {pdf_path}")
        # except Exception as e:
        #     logger.warning(f"删除PDF转换文件失败: {str(e)}")
        
        session.commit()
        return True, "文件完全删除成功"
        
    except Exception as e:
        session.rollback()
        logger.error(f"完全删除文件失败: {str(e)}")
        return False, f"删除失败: {str(e)}"
